# si
import json
import polars as pl
import xlwings as xw
import os
import win32com.client as win32
import util
from util.common import create_folder_if_not_exists


def create_email_template(excel_file_path):
    outlook = win32.Dispatch('Outlook.Application')
    # 创建一封邮件
    mail = outlook.CreateItem(0)
    # 邮件收件人
    mail.To = util.recipient_email if util.recipient_email else ''
    # 获取当前邮件的签名（如果有）
    mail.Display()  # 显示邮件窗口，Outlook会自动加载签名
    # 将自定义内容添加到签名前
    mail.HTMLBody = mail.HTMLBody

    MAX_ATTACHMENT_SIZE = util.MaximumAttachmentSize * 1024 * 1024  # 10MB
    sumsize = 0
    sumsize += os.path.getsize(excel_file_path)
    print(f"附件总大小：{sumsize / 1024 / 1024}MB")
    if sumsize < MAX_ATTACHMENT_SIZE:
        mail.Attachments.Add(excel_file_path)
    else:
        mail.Subject = f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件"
        print(f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件")
    # 显示邮件前预览
    mail.Display()


def create_tem(cons, a2, a5, a16, d16, f16, j17, d19, a19, c19, g19, i19):
    # 启动 Excel 应用
    try:
        app = xw.App(visible=False)
        template_wb = app.books.open(util.BOOKING_FORM_template_path)  # 打开模板文件
        template_sheet = template_wb.sheets['模板收敛-SI']
        # 创建一个新的工作薄
        new_wb = xw.Book()
        template_sheet.api.Copy(Before=new_wb.sheets[0].api)
        new_wb.sheets[1].delete()  # 删除默认的工作表
        sht = new_wb.sheets[0]
        # 向单元格写入数据
        sht.range('A2').value = a2  # Shipper+Shipper Address
        sht.range('A5').value = a5  # 海关申报收货人信息
        sht.range('A16').value = a16  # 最终目的地
        sht.range('D16').value = d16  # 目的地ID

        sht.range('F16').value = f16  # 贸易术语
        sht.range('A19').value = a19  # 箱单号
        sht.range('C19').value = c19  # 计划件数
        sht.range('G19').value = g19  # 计划总毛重（KG）
        sht.range('I19').value = i19  # 计划总体积（CBM）
        sht.range('J17').value = j17  # 如果贸易术语为FCA，就赋值√，否则就为空
        sht.range('D19').value = d19

        # 保存新的工作薄
        excel_name = ""
        for co in cons:
            excel_name = excel_name + co + " & "
        excel_name = excel_name[:-3]
        create_folder_if_not_exists(util.excel_path)
        excel_file_path = os.path.join(util.excel_path, f"SI-{excel_name}.xlsx")
        new_wb.save(excel_file_path)
        return excel_file_path, excel_name
    except Exception as e:
        return f"错误：保存新的工作薄失败：{e}"
    finally:
        new_wb.close()
        template_wb.close()
        app.quit()



def get_shipper():
    df = (
        pl.read_excel(util.shipper_path, sheet_name="Sheet1",
                      read_options={
                          'use_columns': ['公司代码', 'Shipper', '公司名称(中文）']
                      },
                      schema_overrides={"公司代码": pl.String},  # 覆盖默认的列类型
                      engine="calamine"
                      )
        .rename({"公司代码": "company_code", "Shipper": "shipper", "公司名称(中文）": "company_name"})
    )
    return df


def fun_si(datapath, jsonstring):
    df = pl.DataFrame()
    si_df = util.config_df['si']
    tiname = si_df.filter(pl.col("原字段名") == "运输指令名称")["现字段名"].item(0)
    stt = si_df.filter(pl.col("原字段名") == "贸易术语")["现字段名"].item(0)
    dest = si_df.filter(pl.col("原字段名") == "目的地")["现字段名"].item(0)
    dest_id = si_df.filter(pl.col("原字段名") == "最终目的地")["现字段名"].item(0)
    buyer= si_df.filter(pl.col("原字段名") == "客户国进口买方详细信息")["现字段名"].item(0)
    qty = si_df.filter(pl.col("原字段名") == "总件数")["现字段名"].item(0)
    gw = si_df.filter(pl.col("原字段名") == "总重(KG)")["现字段名"].item(0)
    cbm = si_df.filter(pl.col("原字段名") == "总体积(CUMTR)")["现字段名"].item(0)
    con_path= si_df.filter(pl.col("原字段名") == "合同完整路径")["现字段名"].item(0)
    dangerous = si_df.filter(pl.col("原字段名") == "危险品标识")["现字段名"].item(0)
    con= si_df.filter(pl.col("原字段名") == "合同号")["现字段名"].item(0)
    if jsonstring == '无':
        df = (pl.read_excel(datapath, sheet_name="Sheet1",
                            read_options={
                                'use_columns': si_df['现字段名'].to_list()
                            },
                            # schema_overrides={"CPD": pl.Date},  # 覆盖默认的列类型
                            engine="calamine")
              .rename({tiname: 'tiname', stt: 'stt', dest: 'dest', dest_id: 'dest_id',
                       buyer: 'buyer', qty: 'qty', gw: 'gw', cbm: 'cbm',
                       con_path: 'con_path', dangerous: 'dangerous', con: 'con'})  # 重命名列名
              .fill_null('')
              .with_columns(
            pl.col('con_path').str.split_exact('>', 1).struct.rename_fields(['con_path']).alias('con_path')
        )
              .unnest('con_path')
              )
    else:
        json_object = json.loads(jsonstring)
        df = (
            pl.DataFrame(json_object)
            .rename({tiname: 'tiname', stt: 'stt', dest: 'dest', dest_id: 'dest_id',
                     buyer: 'buyer', qty: 'qty', gw: 'gw', cbm: 'cbm',
                     con_path: 'con_path', dangerous: 'dangerous', con: 'con'})  # 重命名列名
            .fill_null('')
            .with_columns(
                pl.col('con_path').str.split_exact('>', 1).struct.rename_fields(['con_path']).alias('con_path')
            )
            .unnest('con_path')
        )
    df_shipper = get_shipper()
    cons = df['con'].unique().to_list()
    a2 = df_shipper.filter(pl.col('company_code') == df['con_path'][0])['shipper'][0]
    a5 = df['buyer'][0]
    a16 = df['dest_id'][0]
    d16 = df['dest'][0]
    f16 = df['stt'][0]
    j17 = ''
    if df['stt'][0] == 'FCA':
        j17 = '√'
    dg = pl.Series(df['dangerous'] == 'DG').any()
    d19 = 'Telecommunication equipment 通讯设备 HS CODE:851762'
    if dg:
        d19 = 'Telecommunication equipment 通讯设备 HS CODE:851762\r\nlithium ion batteries 锂电 HS CODE:850760'
    template_df = (
        df.group_by(['stt'])
        .agg(
            pl.col('tiname').str.concat('\r\n').alias('tiname'),
            pl.col('qty').sum().alias('qty'),
            pl.col('gw').sum().alias('gw'),
            pl.col('cbm').sum().alias('cbm')
        ))
    a19 = template_df['tiname'][0]
    c19 = template_df['qty'][0]
    g19 = template_df['gw'][0]
    i19 = template_df['cbm'][0]
    excel_file_path, con_name = create_tem(cons, a2, a5, a16, d16, f16, j17, d19, a19, c19, g19, i19)
    create_email_template(excel_file_path)
